using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Entities;
using Musicman.Common;
namespace Musicman.DAL
{
	public static class UsersDAO
	{
		#region Insert
		private static DbCommand insertCommand = null;
        private static string insertSQL = "INSERT INTO Users (FIRSTNAME, LASTNAME, EMAILADDRESS, PASSWORD, MAILRQ, ACTIVATIONCODE) VALUES (@FIRSTNAME, @LASTNAME, @EMAILADDRESS, @PASSWORD, @MAILRQ, @ACTIVATIONCODE) SET @PERSONID = SCOPE_IDENTITY()";
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
			if (insertCommand == null)
			{
				insertCommand = db.GetSqlStringCommand(insertSQL);
				db.AddOutParameter(insertCommand, "@PERSONID", DbType.Int64, 8);
				db.AddInParameter(insertCommand, "@FIRSTNAME", DbType.String);
				db.AddInParameter(insertCommand, "@LASTNAME", DbType.String);
				db.AddInParameter(insertCommand, "@EMAILADDRESS", DbType.String);
                db.AddInParameter(insertCommand, "@PASSWORD", DbType.String);
                db.AddInParameter(insertCommand, "@MAILRQ", DbType.String);
                db.AddInParameter(insertCommand, "@ACTIVATIONCODE", DbType.Guid);
			}
			return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, User entity)
		{
			db.SetParameterValue(cmd, "@FIRSTNAME", entity.Firstname);
			db.SetParameterValue(cmd, "@LASTNAME", entity.Lastname);
			db.SetParameterValue(cmd, "@EMAILADDRESS", entity.Emailaddress);
            db.SetParameterValue(cmd, "@PASSWORD", entity.Password);
            db.SetParameterValue(cmd, "@MAILRQ", entity.MailRequest);
            db.SetParameterValue(cmd, "@ACTIVATIONCODE", entity.ActivationCode);
		}
		public static bool InsertUsers(User entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Personid = (long?)db.GetParameterValue(cmd, "@PERSONID");
			}
			return res;
		}
		public static bool InsertUsers(User entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Personid = (long?)db.GetParameterValue(cmd, "@PERSONID");
			}
			return res;
		}
		public static bool InsertListUsers(List<User> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					User entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Personid = (long?)db.GetParameterValue(cmd, "@PERSONID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListUsers(List<User> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				User entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Personid = (long?)db.GetParameterValue(cmd, "@PERSONID");
				}
			}
			return res;
		}
		#endregion
            
		#region Update
		private static DbCommand updateCommand = null;
        private static DbCommand updateStatsCommand = null;
        private static string updateSQL = "UPDATE Users SET FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME, EMAILADDRESS = @EMAILADDRESS, PASSWORD = @PASSWORD, MAILRQ = @MAILRQ, ACTIVATIONCODE = @ACTIVATIONCODE WHERE PERSONID = @PERSONID";
        private static string updateUserStatsProcName = "dbo.UpdateUserStats";

        private static DbCommand GetUpdateStatsCommand(SqlDatabase db)
        {
            if (updateStatsCommand == null)
            {
                updateStatsCommand = db.GetStoredProcCommand(updateUserStatsProcName);
                db.AddInParameter(updateStatsCommand, "@PERSONID", DbType.Int64);
                db.AddInParameter(updateStatsCommand, "@WEB", DbType.Int32);
                db.AddInParameter(updateStatsCommand, "@CLIENT", DbType.Int32);
            }
            return updateStatsCommand;
        }

        private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
			if (updateCommand == null)
			{
				updateCommand = db.GetSqlStringCommand(updateSQL);
				db.AddInParameter(updateCommand, "@PERSONID", DbType.Int64);
				db.AddInParameter(updateCommand, "@FIRSTNAME", DbType.String);
				db.AddInParameter(updateCommand, "@LASTNAME", DbType.String);
				db.AddInParameter(updateCommand, "@EMAILADDRESS", DbType.String);
                db.AddInParameter(updateCommand, "@PASSWORD", DbType.String);
                db.AddInParameter(updateCommand, "@MAILRQ", DbType.String);
                db.AddInParameter(updateCommand, "@ACTIVATIONCODE", DbType.Guid);
			}
			return updateCommand;
		}

		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, User entity)
		{
			db.SetParameterValue(cmd, "@PERSONID", entity.Personid);
			db.SetParameterValue(cmd, "@FIRSTNAME", entity.Firstname);
			db.SetParameterValue(cmd, "@LASTNAME", entity.Lastname);
			db.SetParameterValue(cmd, "@EMAILADDRESS", entity.Emailaddress);
            db.SetParameterValue(cmd, "@PASSWORD", entity.Password);
            db.SetParameterValue(cmd, "@MAILRQ", entity.MailRequest);
            db.SetParameterValue(cmd, "@ACTIVATIONCODE", entity.ActivationCode);
		}

		public static bool UpdateUsers(User entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}

		public static bool UpdateUsers(User entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}

		public static bool UpdateListUsers(List<User> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					User entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListUsers(List<User> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				User entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}

        public static bool UpdateUserStats(long personID, int web, int client)
        {
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetUpdateStatsCommand(db);
            db.SetParameterValue(cmd, "@PERSONID", personID);
            db.SetParameterValue(cmd, "@WEB", web);
            db.SetParameterValue(cmd, "@CLIENT", client);
            return Convert.ToBoolean(db.ExecuteNonQuery(cmd));
        }
		
		#endregion
		
        #region Save
		public static bool SaveUsers(User entity)
		{
			bool res = true;
			if (entity.Personid == null )
			{
				res = InsertUsers(entity);
			}
			else
			{
				res = UpdateUsers(entity);
			}
			return res;
		}
		public static bool SaveUsers(User entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Personid == null )
			{
				res = InsertUsers(entity, transactionObj, db);
			}
			else
			{
				res = UpdateUsers(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListUsers(List<User> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					User entity = list[i];
					res = res & SaveUsers(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListUsers(List<User> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				User entity = list[i];
				res = res & SaveUsers(entity, transactionObj, db);
			}
			return res;
		}
		#endregion

		#region Delete
		private static DbCommand deleteCommand = null;
        private static string deleteProcName = "dbo.deleteUser";
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
			if (deleteCommand == null)
			{
                deleteCommand = db.GetStoredProcCommand(deleteProcName);
				db.AddInParameter(deleteCommand, "@PERSONID", DbType.Int64);
			}
			return deleteCommand;
		}
		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, User entity)
		{
			db.SetParameterValue(cmd, "@PERSONID", entity.Personid);
		}
		public static bool DeleteUsers(User entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool DeleteListUsers(List<User> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					User entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		
		#endregion

		#region Select
        private static string selectSQL = "SELECT PERSONID,FIRSTNAME,LASTNAME,EMAILADDRESS, PASSWORD, MAILRQ, ACTIVATIONCODE FROM Users {0} {1}";
		
		public static List<User> LoadUserss(string where, string orderby)
		{
			List<User> res = new List<User>();
			string sql = String.Format(selectSQL, where, orderby);
			IDataReader reader = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				reader = db.ExecuteReader(CommandType.Text, sql);
				if (reader != null)
				{
					while (reader.Read())
					{
						User entity = new User();
						entity.Personid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
						entity.Firstname = reader.IsDBNull(1) ? null : (string)reader.GetString(1);
						entity.Lastname = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
						entity.Emailaddress = reader.IsDBNull(3) ? null : (string)reader.GetString(3);
                        entity.Password = reader.IsDBNull(4) ? null : (string)reader.GetString(4);
                        entity.MailRequest = reader.GetBoolean(5);
                        entity.ActivationCode = reader.IsDBNull(6) ? null : (Guid?)reader.GetGuid(6);
						res.Add(entity);
					}
				}
				reader.Close();
			}
			catch (Exception ex)
			{
				if (reader != null && !reader.IsClosed)
				{
					reader.Close();
				}
				res = null;
				throw ex;
			}
			return res;
		}

        public static bool CheckUserExist(string emailAddress)
        {
            string sql = String.Format("SELECT PERSONID FROM USERS WHERE EMAILADDRESS = '{0}'", emailAddress);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            object res = db.ExecuteScalar(CommandType.Text, sql);
            return res != null;
        }

        public static bool ActivateNewUser(string activationCode)
        {
            bool res = false;
            string sql = String.Format("UPDATE USERS SET ACTIVATIONCODE = NULL WHERE ACTIVATIONCODE = '{0}'", activationCode);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            res = Convert.ToBoolean(db.ExecuteNonQuery(CommandType.Text, sql));
            return res;
        }

        public static int GetLoginCount(long userID)
        {
            int res = 0;
            string sql = String.Format("SELECT NUMLOGSWEB FROM DBO.USERSTATS WHERE PERSONID = {0}", userID);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            res = (int)db.ExecuteScalar(CommandType.Text, sql);
            return res;
        }

        public static int GetClientLoginCount(long userID)
        {
            int res = 0;
            string sql = String.Format("SELECT NUMLOGSCLIENT FROM DBO.USERSTATS WHERE PERSONID = {0}", userID);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            res = (int)db.ExecuteScalar(CommandType.Text, sql);
            return res;
        }

        public static int GetTotalLoginCount()
        {
            int res = 0;
            string sql = "select sum(NumLogsweb) from dbo.userStats";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            res = (int)db.ExecuteScalar(CommandType.Text, sql);
            return res;
        }

        public static int GetTotalClientLoginCount()
        {
            int res = 0;
            string sql = "select sum(NUMLOGSCLIENT) from dbo.userStats";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            res = (int)db.ExecuteScalar(CommandType.Text, sql);
            return res;
        }

		#endregion
	}
}
